<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" import="java.sql.*" import="Connect.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
<title>Hammer Time Auctions</title>
</head>
<body>
	<div id="content">
		<%@include file="header.jsp"%>
		<h1>Rank Customers Based On Total Revenue Generated</h1>
		<hr></hr>
		<table class="center item-table">
			<tr>
				<th>Customer Username</th>
				<th>Total Price Of All Auctions</th>
				<th>10% Earned By House</th>
			</tr>
			<%
				// Getting information for querying the database.
				java.sql.Connection conn = null;
				try {
					// Connect to the database.
					Class.forName(ConnectionInfo.myJDBCDriver()).newInstance();
					java.util.Properties sysprops = System.getProperties();
					sysprops.put("user", ConnectionInfo.myUserID());
					sysprops.put("password", ConnectionInfo.myPassword());
					conn = java.sql.DriverManager.getConnection(ConnectionInfo.myURL(), sysprops);

					String highest_earner = "None";
					double highest_earned = 0.00;

					// Select each customer.
					java.sql.Statement statement3 = conn.createStatement();
					java.sql.ResultSet rs3 = statement3.executeQuery("SELECT username FROM customers");
					while (rs3.next()) {
						// Keep track of the total.
						double total = 0.00;

						// Select all expired auctions.
						java.sql.Statement statement = conn.createStatement();
						java.sql.ResultSet rs = statement.executeQuery("SELECT * FROM auctions WHERE NOW() > end_date AND owner='"
								+ rs3.getString("username") + "'");
						while (rs.next()) {
							// For each expired auction determine the selling price.
							java.sql.Statement statement2 = conn.createStatement();
							java.sql.ResultSet rs2 = statement2.executeQuery("SELECT * FROM bids WHERE auction_id='" + rs.getString("id")
									+ "' ORDER BY bid_price DESC");
							double current_bid = 0.0;
							if (rs2.next()) {
								// If there is a bid on the auction get the maximum bid.
								current_bid = Double.valueOf(rs2.getString("bid_price"));
								if (rs2.next()) {
									// If there is a second highest bid, calculate the current bid.
									double second_bid = rs2.getDouble("bid_price");
									if (current_bid > second_bid) {
										// If the second highest bid is equal no change is needed. When the second bid is lower than the highest bid calculate
										// the current bid as second_bid + bid_increment
										current_bid = second_bid + rs.getDouble("bid_increment");
									}
								} else {
									// There is no second bid so the current bid is the minimum price.
									current_bid = Double.valueOf(rs.getString("minimum_bid"));
								}
								if (current_bid > rs.getDouble("reserve_price")) {
									total += current_bid;
								}
							}
						}
						// Display the information as a table.
						out.println("<tr>");
						out.println("<td>" + rs3.getString("username") + "</td>");
						out.println("<td>" + ConnectionInfo.twoSigFigs(total) + "</td>");
						out.println("<td>" + ConnectionInfo.twoSigFigs(total * 0.10) + "</td>");
						out.println("</tr>");
						// Compare it with the highest earner.
						total = ConnectionInfo.twoSigFigs(total);
						if (total >= highest_earned) {
							highest_earner = rs3.getString("username");
							highest_earned = total;
						}
					}
					out.println("<tr><th>Highest Earner</th><th class=\"center-text\">Highest Earned</th><th>Most 10% Earned By House</th></tr>");
					out.println("<tr><td>" + highest_earner + "</td>");
					out.println("<td>" + ConnectionInfo.twoSigFigs(highest_earned) + "</td>");
					out.println("<td>" + ConnectionInfo.twoSigFigs(highest_earned * 0.10) + "</td>");
					out.println("</tr>");
				} catch (Exception e) {
					e.printStackTrace();
					out.print(e.toString());
				} finally {
					try {
						conn.close();
					} catch (Exception ee) {
					}
				}
			%>
		</table>
	</div>
	<hr></hr>
	<%@include file="footer.jsp"%>
</body>
</html>